﻿/* Co so du lieu quan ly cua hang trang tri noi that
Date: 06/03/2013

*/
--> Tao CSDL QLCHTTNT
USE MASTER
GO

IF EXISTS (SELECT * FROM SYSDATABASES WHERE NAME = 'QLCHTTNT')
DROP DATABASE QLCHTTNT
GO

CREATE DATABASE QLCHTTNT
GO

--> - Thiet lap kieu ngay thang nam.
SET DATEFORMAT DMY
GO

--> - Dung ALXKHI, tao cac bang du lieu.
USE QLCHTTNT
GO


-->14. TAI_KHOAN
CREATE TABLE TAI_KHOAN
(
	TEN_DANG_NHAP 		VARCHAR(20)	 NOT NULL,
	MAT_KHAU			VARCHAR(50) NOT NULL,
	QUYEN_TRUY_CAP		NVARCHAR(20) DEFAULT 'Nhan vien'
	PRIMARY KEY (TEN_DANG_NHAP),
)

GO

--> 1.CHUC VU
CREATE TABLE CHUC_VU
(
	MA_CV	CHAR    (12)	NOT NULL,
	TEN_CV	NVARCHAR (30)	NULL,
	CONSTRAINT PK_CHUC_VU PRIMARY KEY (MA_CV)
)

GO

--> 2.NHA CUNG CAP
CREATE TABLE NHA_CUNG_CAP
(
	MA_NCC		CHAR    (12)	NOT NULL,
	TEN_NCC		NVARCHAR (50)	NULL,
	DIA_CHI		NVARCHAR (50)	NULL,
	DIEN_THOAI	NVARCHAR (12)	NULL,
	EMAIL		NVARCHAR (30)	NULL,
	CONSTRAINT PK_NHA_CUNG_CAP PRIMARY KEY (MA_NCC)
)

GO

--> 3.CHAT LIEU
CREATE TABLE CHAT_LIEU
(
	MA_CL		CHAR (12)	NOT NULL,
	TEN_CL		NVARCHAR (30)	NULL,
    CONSTRAINT PK_CHAT_LIEU PRIMARY KEY (MA_CL)
)

GO

--> 4.LOAI SAN PHAM
CREATE TABLE LOAI_SAN_PHAM
(
	MA_LSP		CHAR(12)	NOT NULL,
	TEN_LSP		NVARCHAR (30)	NULL,
	CONSTRAINT PK_LOAI_SAN_PHAM PRIMARY KEY (MA_LSP),
)

GO

--> 5.KHACH HANG
CREATE TABLE KHACH_HANG
(
	MA_KH		CHAR (12) DEFAULT 'GUEST' NOT NULL,
	HOTEN_KH	NVARCHAR (50)	NULL,
	DIA_CHI		NVARCHAR (50)	NULL,
	SO_DT	NVARCHAR (12)	NULL,
	EMAIL		NVARCHAR (30)		NULL,
	CONSTRAINT PK_KHACH_HANG PRIMARY KEY (MA_KH)
)

GO



--> 6.NHAN VIEN
CREATE TABLE NHAN_VIEN
(
	MA_NV		CHAR (12)	,
	HOTEN_NV	NVARCHAR (50)	NULL,
	TEN_DANG_NHAP VARCHAR(20) CONSTRAINT FK_NHAN_VIEN_TAI_KHOAN FOREIGN KEY REFERENCES TAI_KHOAN(TEN_DANG_NHAP),
	MA_CV		CHAR(12) CONSTRAINT FK_NHAN_VIEN_CHUC_VU FOREIGN KEY REFERENCES CHUC_VU(MA_CV), 
	GIOI_TINH	VARCHAR(5) DEFAULT 'Nam',
	DIA_CHI		NVARCHAR (50)	NULL,
	SO_DT		NVARCHAR (12)	NULL,
	MUC_LUONG	MONEY			NULL,
	CONSTRAINT PK_NHAN_VIEN PRIMARY KEY (MA_NV)
)

GO

--> 7. SAN_PHAM
CREATE TABLE SAN_PHAM
(
	MA_SP		CHAR(12) PRIMARY KEY NOT NULL,
	TEN_SP		NVARCHAR(30) NULL,
	SO_LUONG_TON INT,
	GIA_MUA_VAO MONEY,
	GIA_BAN_RA MONEY, 
	DON_VI_TINH NVARCHAR(20) NULL,
	NGAY_NHAP_KHO DATETIME DEFAULT 'NGAY_NHAP_KHO = GETDATE()',
	DIEN_GIAI	NVARCHAR(30) NULL,
	MA_LSP		CHAR(12) CONSTRAINT FK_SAN_PHAM_LOAI_SP FOREIGN KEY REFERENCES LOAI_SAN_PHAM(MA_LSP),
	MA_CL		CHAR(12) CONSTRAINT FK_SAN_PHAM_CHAT_LIEU FOREIGN KEY REFERENCES CHAT_LIEU(MA_CL),
	MA_NCC		CHAR(12) CONSTRAINT FK_SAN_PHAM_NHA_CUNG_CAP FOREIGN KEY REFERENCES NHA_CUNG_CAP(MA_NCC),
	

)

GO

--> 8. PHIEU DAT HANG
CREATE TABLE PHIEU_DAT_HANG
(
	STT_PDH			INT PRIMARY KEY IDENTITY,
	NGAY_DAT_HANG	DATETIME CHECK ( NGAY_DAT_HANG <= GETDATE()),
	NGAY_GIAO_HANG	DATETIME ,
	MA_NV			CHAR(12)		NOT NULL
	CONSTRAINT FK_PHIEU_DAT_HANG_NHAN_VIEN FOREIGN KEY REFERENCES NHAN_VIEN(MA_NV),
	MA_KH			CHAR(12)		NOT NULL
	CONSTRAINT FK_PHIEU_DAT_HANG_KHACH_HANG FOREIGN KEY REFERENCES KHACH_HANG(MA_KH),
	TONG_TIEN			MONEY		    NOT NULL,

)

GO

--> 9. CHI_TIET_DAT_HANG
CREATE TABLE CHI_TIET_DAT_HANG
(
	STT_PDH			INT		CONSTRAINT FK_PDH FOREIGN KEY REFERENCES PHIEU_DAT_HANG(STT_PDH),
	MA_SP			CHAR(12) CONSTRAINT FK_MA_SP FOREIGN KEY REFERENCES SAN_PHAM(MA_SP),
	DON_GIA		MONEY,
	SO_LUONG		INT,
	THANH_TIEN		AS SO_LUONG * DON_GIA PERSISTED,
	PRIMARY KEY (STT_PDH, MA_SP),
	
)

GO

--> 10. HOA_DON_NHAP
--DROP TABLE HOA_DON_NHAP
CREATE TABLE HOA_DON_NHAP
(
	STT_HDN			INT PRIMARY KEY IDENTITY,
	NGAY_LAP_HDN		DATETIME CHECK ( NGAY_LAP_HDN <= GETDATE()),
	MA_NV			CHAR(12)		NOT NULL 
	CONSTRAINT FK_PHIEU_DAT_HANG_MA_NHAN_VIEN FOREIGN KEY REFERENCES NHAN_VIEN(MA_NV),
	MA_NCC			CHAR(12)		NOT NULL
	CONSTRAINT FK_HOA_DON_NHAP_NHA_CC FOREIGN KEY REFERENCES NHA_CUNG_CAP(MA_NCC),
	VAT CHAR(10) DEFAULT '5%',
	TONG_TIEN			MONEY		    NOT NULL,

)

GO

-->11. CHI_TIET_HOD_DON_NHAP
CREATE TABLE CHI_TIET_HOA_DON_NHAP
(
	STT_HDN			INT			REFERENCES PHIEU_DAT_HANG NOT NULL,
	MA_SP			CHAR(12)	REFERENCES SAN_PHAM NOT NULL,
	GIA_BAN			MONEY,
	SO_LUONG		INT		CHECK (SO_LUONG >0),
	THANH_TIEN		AS SO_LUONG * GIA_BAN PERSISTED,
	PRIMARY KEY (STT_HDN, MA_SP),
	
)

GO

--> 12. HOA_DON_BAN
CREATE TABLE HOA_DON_BAN
(
	STT_HDB			INT PRIMARY KEY IDENTITY,
	NGAY_LAP_HDB		DATETIME CHECK ( NGAY_LAP_HDB <= GETDATE()),
	MA_NV			CHAR(12)		NOT NULL 
	CONSTRAINT FK_HOA_DON_BAN_NHAN_VIEN FOREIGN KEY REFERENCES NHAN_VIEN(MA_NV),
	MA_KH			CHAR(12)		NOT NULL
	CONSTRAINT FK_HOA_DON_BAN_KHACH_HANG FOREIGN KEY REFERENCES KHACH_HANG(MA_KH),
	VAT CHAR(10) DEFAULT '5%',
	TONG_TIEN			MONEY		    NOT NULL,

)

GO

-->13. CHI_TIET_HOA_DON_BAN
CREATE TABLE CHI_TIET_HOA_DON_BAN
(
	STT_HDB 		INT			REFERENCES HOA_DON_BAN NOT NULL,
	MA_SP			CHAR(12)	REFERENCES SAN_PHAM NOT NULL,
	GIA_BAN			MONEY,
	SO_LUONG		INT		CHECK (SO_LUONG >0),
	THANH_TIEN		AS SO_LUONG * GIA_BAN PERSISTED,
	PRIMARY KEY (STT_HDB, MA_SP),
	
)

-------------------------------------------
-- NHAP DU LIEU
-->1. CHUC VU

INSERT INTO CHUC_VU(MA_CV, TEN_CV) VALUES
('KT', N'Kế Toán');
INSERT INTO CHUC_VU(MA_CV, TEN_CV) VALUES
('BH', N'Bán Hàng');
INSERT INTO CHUC_VU(MA_CV, TEN_CV) VALUES
('CN1', N'Công Nhân');
INSERT INTO CHUC_VU(MA_CV, TEN_CV) VALUES
('CN2', N'Công Nhân');
INSERT INTO CHUC_VU(MA_CV, TEN_CV) VALUES
('QL', N'Quản Lý');
------------------------------------------
--2. tai khoan
insert into TAI_KHOAN (TEN_DANG_NHAP, MAT_KHAU, QUYEN_TRUY_CAP) values
('thanhphong','123456789',1)
insert into TAI_KHOAN (TEN_DANG_NHAP, MAT_KHAU, QUYEN_TRUY_CAP) values
('thanhthanh','123456',0);
insert into TAI_KHOAN (TEN_DANG_NHAP, MAT_KHAU, QUYEN_TRUY_CAP) values
('cua','cua',0);
insert into TAI_KHOAN (TEN_DANG_NHAP, MAT_KHAU, QUYEN_TRUY_CAP) values
('lamtamnham','987654321',0);
insert into TAI_KHOAN (TEN_DANG_NHAP, MAT_KHAU, QUYEN_TRUY_CAP) values
('tala','12345',0);
insert into TAI_KHOAN (TEN_DANG_NHAP, MAT_KHAU, QUYEN_TRUY_CAP) values
('admin','admin',0);
-------------------------------------------------------
--3. CHAT LIEU
insert into CHAT_LIEU(MA_CL, TEN_CL) VALUES
('AL',N'Nhôm');
insert into CHAT_LIEU(MA_CL, TEN_CL) VALUES
('Inox',N'Inox');
insert into CHAT_LIEU(MA_CL, TEN_CL) VALUES
('FE',N'Sắt');
insert into CHAT_LIEU(MA_CL, TEN_CL) VALUES
('GO',N'Gỗ');
insert into CHAT_LIEU(MA_CL, TEN_CL) VALUES
('KIENG', N'Kiếng');
----------------------------------------------------
--4. nha cung cap
insert into NHA_CUNG_CAP(MA_NCC, TEN_NCC, DIA_CHI, DIEN_THOAI, EMAIL) VALUES
('NA', N'Ngọc Ánh',N'BÌNH MINH, VĨNH LONG','01222233339','ngoc_anh@yahoo.com');
go
insert into NHA_CUNG_CAP(MA_NCC, TEN_NCC, DIA_CHI, DIEN_THOAI, EMAIL) VALUES
('TL', N'Thành Long',N'BÌNH MINH, VĨNH LONG','0703750013','thanh_long@yahoo.com');
go
insert into NHA_CUNG_CAP(MA_NCC, TEN_NCC, DIA_CHI, DIEN_THOAI, EMAIL) VALUES
('KL', N'Kim Loan',N'Vĩnh Kim, Tiền Giang','0909789456','kim_loan@yahoo.com');
go
insert into NHA_CUNG_CAP(MA_NCC, TEN_NCC, DIA_CHI, DIEN_THOAI, EMAIL) VALUES
('TB', N'Thái Bình',N'Ninh Kiều - Cần Thơ','0979777888','thai_binh@yahoo.com');
go
insert into NHA_CUNG_CAP(MA_NCC, TEN_NCC, DIA_CHI, DIEN_THOAI, EMAIL) VALUES
('NT', N'Nkox Tox',N'BÌNH MINH, VĨNH LONG','0932885576','nkoxtox@yahoo.com');
go

--------------------------------------
--5. LOAI SAN PHAM
INSERT INTO LOAI_SAN_PHAM(MA_LSP, TEN_LSP) VALUES
('K1', N'Tủ Kệ');
go
INSERT INTO LOAI_SAN_PHAM(MA_LSP, TEN_LSP) VALUES
('A1',N'Tủ Áo');
go
INSERT INTO LOAI_SAN_PHAM(MA_LSP, TEN_LSP) VALUES
('BK1',N'Bàn Kiếng');
go
INSERT INTO LOAI_SAN_PHAM(MA_LSP, TEN_LSP) VALUES
('G1', N'Ghế');
go
INSERT INTO LOAI_SAN_PHAM(MA_LSP, TEN_LSP) VALUES
('KS', N'Két Sắt');
go
-----------------------------------
--6. Nhan Vien
INSERT INTO NHAN_VIEN(MA_NV, HOTEN_NV, TEN_DANG_NHAP, MA_CV, GIOI_TINH, SO_DT, DIA_CHI, MUC_LUONG) VALUES
('NV1',N'Nguyễn Thanh Phong', 'thanhphong', 'KT', 'Nam', '0932885576',N'Thuận An - Bình Minh', '10')

GO
INSERT INTO NHAN_VIEN(MA_NV, HOTEN_NV, TEN_DANG_NHAP, MA_CV, GIOI_TINH, SO_DT, DIA_CHI, MUC_LUONG) VALUES
('NV2',N'Lâm Tâm Nhâm', 'lamtamnham', 'KT', 'Nam', '01234567890',N'Đồng Tháp', '20')

GO
INSERT INTO NHAN_VIEN(MA_NV, HOTEN_NV, TEN_DANG_NHAP, MA_CV, GIOI_TINH, SO_DT, DIA_CHI, MUC_LUONG) VALUES
('NV3',N'Hoài Thanh', 'thanhthanh', 'CN1', 'Nam', '0167898123',N'Châu thành - Đồng Tháp', '25')

GO
INSERT INTO NHAN_VIEN(MA_NV, HOTEN_NV, TEN_DANG_NHAP, MA_CV, GIOI_TINH, SO_DT, DIA_CHI, MUC_LUONG) VALUES
('NV4',N'Trung Trực', 'cua', 'BH', 'Nam', '0988456639',N'Mù U - Tam Bình', '10')

GO
INSERT INTO NHAN_VIEN(MA_NV, HOTEN_NV, TEN_DANG_NHAP, MA_CV, GIOI_TINH, SO_DT, DIA_CHI, MUC_LUONG) VALUES
('NV5',N'Tà La', 'tala', 'CN2', 'Nam', '01885885333',N'Thuận An A - Bình Minh', '20')

GO
-------------------------------------
-- 7. san pham
INSERT INTO SAN_PHAM(MA_SP, TEN_SP, SO_LUONG_TON, GIA_MUA_VAO, GIA_BAN_RA, DON_VI_TINH, NGAY_NHAP_KHO, DIEN_GIAI, MA_LSP, MA_CL, MA_NCC) VALUES
('SP1', N'Tủ Áo', 10, '2500000', '300', N'Cái', '12/03/2013', N'Trắng, Cao 175m, bề ngang 1m6', 'A1', 'AL', 'NA' )

GO

INSERT INTO SAN_PHAM(MA_SP, TEN_SP, SO_LUONG_TON, GIA_MUA_VAO, GIA_BAN_RA, DON_VI_TINH, NGAY_NHAP_KHO, DIEN_GIAI, MA_LSP, MA_CL, MA_NCC) VALUES
('SP2', N'Tủ Kiếng', 10, '3500000', '400', N'Cái', '12/03/2013', N'Nâu, Cao 175m, bề ngang 1m8', 'A1', 'GO', 'NA' )

GO

INSERT INTO SAN_PHAM(MA_SP, TEN_SP, SO_LUONG_TON, GIA_MUA_VAO, GIA_BAN_RA, DON_VI_TINH, NGAY_NHAP_KHO, DIEN_GIAI, MA_LSP, MA_CL, MA_NCC) VALUES
('SP3', N'Ghế', 100, '25000', '300', N'Cái', '12/03/2013',N'Trắng, Inox, Chân Kiềng', 'A1', 'AL', 'NA' )

GO

INSERT INTO SAN_PHAM(MA_SP, TEN_SP, SO_LUONG_TON, GIA_MUA_VAO, GIA_BAN_RA, DON_VI_TINH, NGAY_NHAP_KHO, DIEN_GIAI, MA_LSP, MA_CL, MA_NCC) VALUES
('SP4', N'Tủ Áo', 10, '2500000', '300', N'Cái', '12/03/2013',N'Trắng, Cao 175m, bề ngang 1m6', 'A1', 'AL', 'NA' )

GO

-------------------------------------
--> 8 . KHACH_HANG
INSERT INTO KHACH_HANG (MA_KH, HOTEN_KH, DIA_CHI, SO_DT, EMAIL) VALUES
('KH1', N'Nguyễn Văn Hòa', N'Thuận An', '01235678956', 'hoa@yahoo.com')

GO

INSERT INTO KHACH_HANG (MA_KH, HOTEN_KH, DIA_CHI, SO_DT, EMAIL) VALUES
('KH2', N'Nguyễn Văn	Phong', N'Thuận Tiến', '01235678956', 'hoa@yahoo.com')

GO

INSERT INTO KHACH_HANG (MA_KH, HOTEN_KH, DIA_CHI, SO_DT, EMAIL) VALUES
('KH3', N'Nguyễn Thành Công', N'Thuận An', '01235678956', 'hoa@yahoo.com')

GO

-------------------------------------
--9. phieu dat hang
INSERT INTO PHIEU_DAT_HANG(NGAY_DAT_HANG, NGAY_GIAO_HANG, MA_NV, MA_KH, TONG_TIEN) VALUES
('13/03/2013', '14/03/2013', 'NV1','KH1',100)
GO
INSERT INTO PHIEU_DAT_HANG(NGAY_DAT_HANG, NGAY_GIAO_HANG, MA_NV, MA_KH, TONG_TIEN) VALUES
('12/03/2013', '16/03/2013', 'NV2','KH2',700)
GO
INSERT INTO PHIEU_DAT_HANG(NGAY_DAT_HANG, NGAY_GIAO_HANG, MA_NV, MA_KH, TONG_TIEN) VALUES
('11/03/2013', '17/03/2013', 'NV3','KH3',700)
GO
-----------------------
-->10. chi tiet phieu dat hang
insert into CHI_TIET_DAT_HANG(STT_PDH, MA_SP, DON_GIA, SO_LUONG) VALUES
(2, 'SP1',300, 1)
GO

insert into CHI_TIET_DAT_HANG(STT_PDH, MA_SP, DON_GIA, SO_LUONG) VALUES
(2, 'SP2',400, 1)
GO
insert into CHI_TIET_DAT_HANG(STT_PDH, MA_SP, DON_GIA, SO_LUONG) VALUES
(3, 'SP2',400, 1)
GO
insert into CHI_TIET_DAT_HANG(STT_PDH, MA_SP, DON_GIA, SO_LUONG) VALUES
(3, 'SP3',300, 1)
----------------------------------------
GO
--11. HOA_DON_BAN
INSERT INTO HOA_DON_BAN(NGAY_LAP_HDB,  MA_NV, MA_KH, VAT, TONG_TIEN) VALUES
('13/03/2013','NV4','KH1', '5%', 600)
GO
INSERT INTO HOA_DON_BAN(NGAY_LAP_HDB,  MA_NV, MA_KH, VAT, TONG_TIEN) VALUES
('13/03/2013', 'NV1','KH3', '5%', 300)
GO
INSERT INTO HOA_DON_BAN(NGAY_LAP_HDB,  MA_NV, MA_KH, VAT, TONG_TIEN) VALUES
('13/03/2013', 'NV4','KH2', '5%', 800)
GO
-----------------------
-->12. chi tiet HOA DON BAN
insert into CHI_TIET_HOA_DON_BAN(STT_HDB, MA_SP, GIA_BAN, SO_LUONG) VALUES
(1, 'SP1',300, 2)
GO

insert into CHI_TIET_HOA_DON_BAN(STT_HDB, MA_SP, GIA_BAN, SO_LUONG) VALUES
(2, 'SP3',300, 1)
GO
insert into CHI_TIET_HOA_DON_BAN(STT_HDB, MA_SP, GIA_BAN, SO_LUONG) VALUES
(3, 'SP2',400, 2)
GO

----------------------------------------
GO
--13. HOA_DON_BAN
INSERT INTO HOA_DON_NHAP(NGAY_LAP_HDN,  MA_NV, MA_NCC, VAT, TONG_TIEN) VALUES
('13/03/2013','NV4','TL', '5%', 600)
GO
INSERT INTO HOA_DON_NHAP(NGAY_LAP_HDN,  MA_NV, MA_NCC, VAT, TONG_TIEN) VALUES
('13/03/2013', 'NV1','NA', '5%', 300)
GO
INSERT INTO HOA_DON_NHAP(NGAY_LAP_HDN,  MA_NV, MA_NCC, VAT, TONG_TIEN) VALUES
('12/03/2013', 'NV4','NA', '5%', 800)
GO
-----------------------
-->14. chi tiet HOA DON BAN
insert into CHI_TIET_HOA_DON_NHAP(STT_HDN, MA_SP, GIA_BAN, SO_LUONG) VALUES
(1, 'SP1',300, 2)
GO

insert into CHI_TIET_HOA_DON_NHAP(STT_HDN, MA_SP, GIA_BAN, SO_LUONG) VALUES
(2, 'SP3',300, 1)
GO
insert into CHI_TIET_HOA_DON_NHAP(STT_HDN, MA_SP, GIA_BAN, SO_LUONG) VALUES
(3, 'SP2',400, 2)
GO